/*
 Navicat Premium Data Transfer

 Source Server         : test
 Source Server Type    : PostgreSQL
 Source Server Version : 110016
 Source Host           : localhost:5432
 Source Catalog        : postgres
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 110016
 File Encoding         : 65001

 Date: 10/06/2022 15:12:24
*/


-- ----------------------------
-- Table structure for family
-- ----------------------------
DROP TABLE IF EXISTS "public"."family";
CREATE TABLE "public"."family" (
  "family_no" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
  "family_name" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
  "family_describe" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "is_del" bool NOT NULL DEFAULT false,
  "update_time" timestamptz(6) NOT NULL
)
;

-- ----------------------------
-- Records of family
-- ----------------------------
INSERT INTO "public"."family" VALUES ('202', '宋', '宋氏家族特指民国时期的宋霭龄、宋庆龄、宋美龄、宋子文等人的家族。在民国时期，宋氏家族被称之为“民国第一家族”。', 'f', '2022-06-07 11:51:18.179607+08');
INSERT INTO "public"."family" VALUES ('201', '蒋', '蒋氏家族是中国近现代唯一统治过海峡两岸的家族，也是中国最后一个能“家天下”的家族', 'f', '2021-06-20 08:00:00+08');

-- ----------------------------
-- Table structure for member
-- ----------------------------
DROP TABLE IF EXISTS "public"."member";
CREATE TABLE "public"."member" (
  "member_no" varchar(10) COLLATE "pg_catalog"."default" NOT NULL UNIQUE,
  "member_name" varchar(10) COLLATE "pg_catalog"."default",
  "member_sex" varchar(2) COLLATE "pg_catalog"."default",
  "birthday" varchar(200) COLLATE "pg_catalog"."default",
  "deathday" varchar(10) COLLATE "pg_catalog"."default",
  "dept_name" varchar(10) COLLATE "pg_catalog"."default",
  "is_del" bool NOT NULL DEFAULT false,
  "update_time" timestamptz(6) NOT NULL
)
;

-- ----------------------------
-- Records of member
-- ----------------------------
INSERT INTO "public"."member" VALUES ('2010001', '蒋介石', '男', '1887.10.31', '1975.4.5','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2010002', '蒋经国', '男', '1910.4.27', '1988.1.13','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2010003', '蒋纬国', '男', '1916.10.6', '1997.9.23','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2010004', '蒋孝文', '男', '', '','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2010005', '蒋孝章', '女', '', '','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2010006', '蒋孝武', '男', '', '','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2010007', '蒋孝勇', '男', '', '','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2010008', '蒋孝严', '男', '', '','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2010009', '蒋孝慈', '男', '', '','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2010010', '蒋孝刚', '男', '', '','蒋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2020001', '宋嘉树', '男', '', '','宋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2020002', '宋庆龄', '女', '', '','宋', 'f', '2022-06-10 08:00:00+08');
INSERT INTO "public"."member" VALUES ('2020003', '宋美龄', '女', '', '','宋', 'f', '2022-06-10 08:00:00+08');

-- ----------------------------
-- Table structure for menu
-- ----------------------------
DROP TABLE IF EXISTS "public"."menu";
CREATE TABLE "public"."menu" (
  "menu_id" int4 NOT NULL,
  "menu_address" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "menu_level" int4 NOT NULL,
  "menu_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL
)
;

-- ----------------------------
-- Records of menu
-- ----------------------------
INSERT INTO "public"."menu" VALUES (1, '/member', 2, '成员信息');
INSERT INTO "public"."menu" VALUES (2, '/family', 2, '家族信息');
INSERT INTO "public"."menu" VALUES (3, '/relation', 2, '成员关系表');
INSERT INTO "public"."menu" VALUES (4, '/tree', 2, '家谱图');

-- ----------------------------
-- Table structure for relation
-- ----------------------------
DROP TABLE IF EXISTS "public"."relation";
CREATE TABLE "public"."relation" (
  "relation_no" varchar(12) COLLATE "pg_catalog"."default" NOT NULL UNIQUE,
  "relation_name1" varchar(10) COLLATE "pg_catalog"."default",
  "relation_name2" varchar(10) COLLATE "pg_catalog"."default",
  "relation" varchar(10) COLLATE "pg_catalog"."default",
  "is_del" bool NOT NULL DEFAULT false,
  "update_time" timestamptz(6) NOT NULL
)
;

-- ----------------------------
-- Records of relation
-- ----------------------------
INSERT INTO "public"."relation" VALUES ('201000101', '蒋介石', '蒋经国', '父子', 'f', '2021-06-20 08:00:00+08');
INSERT INTO "public"."relation" VALUES ('201000102', '蒋介石', '蒋纬国', '父子', 'f', '2021-06-20 08:00:00+08');
INSERT INTO "public"."relation" VALUES ('201000201', '蒋经国', '蒋孝文', '父子', 'f', '2021-06-20 08:00:00+08');
INSERT INTO "public"."relation" VALUES ('201000301', '蒋纬国', '蒋孝刚', '父子', 'f', '2021-06-20 08:00:00+08');
INSERT INTO "public"."relation" VALUES ('201000401', '蒋孝文', '蒋友梅', '父子', 'f', '2021-06-20 08:00:00+08');
INSERT INTO "public"."relation" VALUES ('201001001', '蒋孝刚', '蒋友捷', '父子', 'f', '2021-06-20 08:00:00+08');
INSERT INTO "public"."relation" VALUES ('201001002', '蒋孝刚', '蒋友娟', '父女', 'f', '2021-06-20 08:00:00+08');
INSERT INTO "public"."relation" VALUES ('201000103', '蒋介石', '宋美龄', '夫妻', 'f', '2021-06-20 08:00:00+08');
INSERT INTO "public"."relation" VALUES ('202000201', '宋庆龄', '宋美龄', '姐妹', 'f', '2021-06-20 08:00:00+08');

-- ----------------------------
-- Table structure for staff
-- ----------------------------
DROP TABLE IF EXISTS "public"."staff";
CREATE TABLE "public"."staff" (
  "staff_id" int4 NOT NULL,
  "staff_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "staff_password" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "staff_level" int4 NOT NULL,
  "staff_remarks" varchar(255) COLLATE "pg_catalog"."default",
  "is_del" bool NOT NULL DEFAULT false,
  "update_time" timestamptz(0) NOT NULL
)
;

-- ----------------------------
-- Records of staff
-- ----------------------------
INSERT INTO "public"."staff" VALUES (1, 'admin', 'admin', 2, '管理员', 'f', '2021-06-30 11:04:59+08');
INSERT INTO "public"."staff" VALUES (20210702, 'testtest', 'testtest', 1, '普通用户', 'f', '2021-06-30 11:06:24+08');
INSERT INTO "public"."staff" VALUES (20210656, 'lemon', 'lemon', 1, '普通用户', 'f', '2021-06-30 11:06:24+08');



DROP TABLE IF EXISTS "public"."describe";
CREATE TABLE "public"."describe" (
  "describe_no" varchar(10) COLLATE "pg_catalog"."default" NOT NULL UNIQUE,
  "describe_name" varchar(10) COLLATE "pg_catalog"."default",
  "time" varchar(12) COLLATE "pg_catalog"."default",
  "type" varchar(10) COLLATE "pg_catalog"."default",
  "describe" varchar(100) COLLATE "pg_catalog"."default",
  "is_del" bool NOT NULL DEFAULT false,
  "update_time" timestamptz(6) NOT NULL
)
;


-- ----------------------------
-- Function structure for delete_relation
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."delete_relation"();
CREATE OR REPLACE FUNCTION "public"."delete_relation"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
BEGIN
	DELETE FROM relation WHERE relation_name1 = OLD.relation_name1;
	RETURN OLD;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Primary Key structure for table family
-- ----------------------------
ALTER TABLE "public"."family" ADD CONSTRAINT "family_pkey" PRIMARY KEY ("family_no");

-- ----------------------------
-- Triggers structure for table member
-- ----------------------------
CREATE TRIGGER "delete_course_trigger" AFTER DELETE ON "public"."member"
FOR EACH STATEMENT
EXECUTE PROCEDURE "public"."delete_relation"();

-- ----------------------------
-- Primary Key structure for table member
-- ----------------------------
ALTER TABLE "public"."member" ADD CONSTRAINT "member_pkey" PRIMARY KEY ("member_no");

-- ----------------------------
-- Primary Key structure for table staff
-- ----------------------------
ALTER TABLE "public"."staff" ADD CONSTRAINT "staff_pkey" PRIMARY KEY ("staff_id");
